"""

name: qb_database_
description: connect qube database, more fast get data from database

"""

import sys
sys.path.append('/sw/systems/shared/python/')
import time
import config

sys.path.append(config.QB_GUI_PATH)
import qbMySQL


class QubeDatabase():
    def __init__(self):
        self.cursor = None
        self.conn = None
        self.init_database()
        self.last_id = self.last_jobid()

    def init_database(self):
        self.conn = qbMySQL.dbConnection()
        self.cursor = self.conn.cursor()

    def check_update(self):
        self.cursor.execute("SELECT * FROM jobidmax")
        if self.last_id != int(self.cursor.fetchall()[0][0]):
            if self.conn:
                self.conn.close()
            self.conn = qbMySQL.dbConnection()
            self.cursor = self.conn.cursor()

    def last_jobid(self):
        self.cursor.execute("SELECT * FROM jobidmax")
        return int(self.cursor.fetchall()[0][0])

    def job_info(self, limit=0, status_code=0, query='*'):
        self.check_update()
        # if not limit value is get last data
        jobid = self.last_id - int(limit)
        # if status_code == 0 is get all status jobs
        if status_code == 0:
            self.cursor.execute("SELECT %s FROM job WHERE id>=%s" % (query, jobid))
        else:
            self.cursor.execute("SELECT %s FROM job WHERE id>=%s AND status=%s" % (query, jobid, status_code))
        return self.cursor.fetchall()

    def host_info(self, query_field='*', query=''):
        self.check_update()
        if query:
            self.cursor.execute("SELECT %s FROM host %s" % (query_field, query))
        else:
            self.cursor.execute("SELECT %s FROM host" % query_field)
        return self.cursor.fetchall()

    def host(self, query_field='*', query=''):
        self.check_update()
        if query:
            self.cursor.execute("SELECT %s FROM host %s" % (query_field, query))
        else:
            self.cursor.execute("SELECT %s FROM host" % query_field)
        return self.cursor.fetchall()

    def cpu_info(self, jobid):
        self.cursor.execute("SELECT id,cpustally,todotally,todo FROM job WHERE id=%s" % jobid)
        return self.cursor.fetchall()

    def all_node(self):
        c = 0
        query = "WHERE name LIKE 'node%' AND locks='host.processor_all=0' AND state=1"
        for i in self.host_info('total_cpus', query):
            c += int(i[0])
        return c

    def running_node(self):
        c = 0
        self.cursor.execute("SELECT cpustally FROM job WHERE status=336")
        for i in self.cursor.fetchall():
            c += int(i[0].split(' ')[0])
        return c

    def pending_node(self):
        c = 0
        self.cursor.execute("SELECT todotally FROM job WHERE status=336 or status=320 or status=272")
        for i in self.cursor.fetchall():
            c += int(i[0].split(' ')[3])
        return c

    def wdn_node(self):
        c = 0
        self.cursor.execute("SELECT cpustally FROM job WHERE status=336 AND name like '%WDN%'")
        for i in self.cursor.fetchall():
            c += int(i[0].split(' ')[3])
        return c

    def aha_node(self):
        c = 0
        self.cursor.execute("SELECT cpustally FROM job WHERE status=336 AND name like '%AHA%'")
        for i in self.cursor.fetchall():
            c += int(i[0].split(' ')[0])
        return c

    def bri_node(self):
        c = 0
        self.cursor.execute("SELECT cpustally FROM job WHERE status=336 AND name like '%BRI%'")
        for i in self.cursor.fetchall():
            c += int(i[0].split(' ')[0])
        return c

    def katana_node(self):
        c = 0
        self.cursor.execute("SELECT cpustally FROM job WHERE status=336 AND name like 'Katana%'")
        for i in self.cursor.fetchall():
            c += int(i[0].split(' ')[0])
        return c

    def mr_node(self):
        c = 0
        self.cursor.execute("SELECT cpustally FROM job WHERE status=336 AND name like 'MR%'")
        for i in self.cursor.fetchall():
            c += int(i[0].split(' ')[0])
        return c

    def pending_job(self):
        self.check_update()
        self.cursor.execute("SELECT id FROM job WHERE status=320")
        return self.cursor.fetchall()

    def running_job(self,query=''):
        self.check_update()
        self.cursor.execute("SELECT id FROM job WHERE status=336 ")
        return self.cursor.fetchall()

    def complete_job(self):
        self.check_update()
        self.cursor.execute("SELECT id FROM job WHERE status=16")
        return self.cursor.fetchall()

    def wdn_pend(self,query_field='id,user,priority,name,todo,subjobstatus,cluster,running,cpus,timesubmit,timestart,timecomplete,status',query="WHERE status=320 AND name like '%WDN%'"):
        self.check_update()
        self.cursor.execute("SELECT %s FROM job %s" % (query_field, query))
        return self.cursor.fetchall()

    def job_cluster(self, limit, cluster='/'):
        c = 0
        for i in self.job_info(limit, 336):
            if i[4] == cluster:
                cpu_running = i[40].split(' ')[0]
                c += int(cpu_running)
            elif cluster == '/kat' and i[13][:6] == 'Katana':
                cpu_running = i[40].split(' ')[0]
                c += int(cpu_running)
        return c

    def job_agenda(self, jobid, query_field='*', query=''):
        self.init_database()
        try:
            jobid = int(jobid)
        except Exception, e:
            return e
        try :
            self.cursor.execute('SELECT %s FROM %iqube.%iwork %s' % (query_field, jobid >> 7, jobid, query))
            return self.cursor.fetchall()
        except Exception, e:
            return e

    def job_query(self, query_field='*', query=''):
        self.check_update()
        self.cursor.execute("SELECT %s FROM job %s" % (query_field, query))
        return self.cursor.fetchall()

    def node_groups(self, groups):
        self.check_update()
        self.cursor.execute("SELECT * FROM host WHERE groups='%s,production' AND locks='host.processor_all=0'" % groups)
        return self.cursor.fetchall()

    def show_tb(self):
        self.cursor.execute('use qube')
        self.cursor.execute("show TABLES")
        return self.cursor.fetchall()

    def show_db(self):
        self.cursor.execute("SHOW DATABASES")
        return self.cursor.fetchall()

    def view(self):
        self.cursor.execute("DESCRIBE job")
        return self.cursor.fetchall()

    def view_host(self):
        self.cursor.execute('describe host')
        return self.cursor.fetchall()

    def nowtime(self):
        self.check_update()
        self.cursor.execute("select now()+0")
        return self.cursor.fetchall()

    def job_submitime(self, query_field='timesubmit', query=''):
        self.check_update()
        self.cursor.execute("SELECT %s FROM job %s" % (query_field, query))
        return self.cursor.fetchall()

    def job_timestart(self, query_field='timestart', query=''):
        self.check_update()
        self.cursor.execute("SELECT %s FROM job %s" % (query_field, query))
        return self.cursor.fetchall()

    def job_timecomplete(self, query_field='timecomplete', query=''):
        self.check_update()
        self.cursor.execute("SELECT %s FROM job %s" % (query_field, query))
        return self.cursor.fetchall()

    def show_db(self):
        self.cursor.execute("show databases")
        return self.cursor.fetchall()

    def pfx_show(self):
        self.cursor.execute("use pfx_dw")
        return self.cursor.fetchall()

    def job_report(self, query_field='id,user,priority,name,todo,subjobstatus,cluster,running,cpus,timesubmit,timestart,timecomplete,status', query=''):
        self.check_update()
        self.cursor.execute("SELECT %s FROM job %s" % (query_field, query))
        return self.cursor.fetchall()

    def job_report_2(self, query_field='id,timesubmit,timestart,timecomplete,status', query=''):
        self.check_update()
        self.cursor.execute("SELECT %s FROM job %s" % (query_field, query))
        return self.cursor.fetchall()

    def shot_frame(self, query_field='id,prod_shot,todo,todotally,timesubmit,timestart,timecomplete,name', query=''):
        self.check_update()
        self.cursor.execute("SELECT %s FROM job %s" % (query_field, query))
        return self.cursor.fetchall()

    def shot_frame_2(self, query_field='id,prod_shot,todo,todotally,status,timesubmit,timestart,timecomplete,name', query=''):
        self.check_update()
        self.cursor.execute("SELECT %s FROM job %s" % (query_field, query))
        return self.cursor.fetchall()

    def time_out(self, query_field='id,user,status,name', query=''):
        self.check_update()
        self.cursor.execute("SELECT %s FROM job %s" % (query_field, query))
        return self.cursor.fetchall()

    def auto_job(self, query_field='id,user,status,name,timestart', query=''):
        self.check_update()
        self.cursor.execute("SELECT %s FROM job %s" % (query_field, query))
        return self.cursor.fetchall()

    def PMD_count_lgt(self,query_field='id,user,status,name,prod_shot,prod_dept,todo,todotally,timesubmit,timestart,timecomplete', query=''):
        self.check_update()
        self.cursor.execute("SELECT %s FROM job %s" % (query_field, query))
        return self.cursor.fetchall()

    def PMD_count_lgt_test(self,query_field='id,prod_dept,prod_shot,name', query=''):
        self.check_update()
        self.cursor.execute("SELECT %s FROM job %s" % (query_field, query))
        return self.cursor.fetchall()

    def close(self):
        self.cursor.close()
        self.conn.close()

class instance(QubeDatabase):
    def __init__(self):
        self.cursor = None
        self.conn = None
        self.init_database()
        self.last_id = self.last_jobid()

    def init_database(self):
        self.conn = qbMySQL.dbConnection()
        self.cursor = self.conn.cursor()

    def host_all(self):
        host_info = self.host_info(query_field='name,state,locks')
        c = 0
        for i in host_info:
            c += 1
        return c

    def host_base(self):
        host_info_base = self.host_info(query_field='name,state,locks,properties', query="WHERE name LIKE 'base%'")
        c_base = 0
        for i in host_info_base:
            c_base += 1
        return c_base

    def host_node(self):
        host_info_node = self.host_info(query_field='name,state,locks,properties', query="WHERE name LIKE 'node%'")
        c_node = 0
        for i in host_info_node:
            c_node += 1
        return c_node

    def host_node_MR(self):
        host_info_node = self.host_info(query_field='name,state,locks', query="WHERE cluster = '/MR'")
        c_node = 0
        for i in host_info_node:
            c_node += 1
        return c_node

    def host_node_Nuke(self):
        host_info_node = self.host_info(query_field='name,state,locks', query="WHERE cluster = '/NK'")
        c_node = 0
        for i in host_info_node:
            c_node += 1
        return c_node

    def host_node_wsenv(self):
        host_info_node = self.host_info(query_field='name,state,locks', query="WHERE cluster = '/wsenv'")
        c_node = 0
        for i in host_info_node:
            c_node += 1
        return c_node

    def host_can_use(self):
        return self.host_all() - (self.host_base_down() + self.host_node_down() + self.host_base_lock() + self.host_node_lock())

    def host_base_lock(self):
        host_info_lock_base = self.host_info(query_field='name,state,locks',
                                          query="WHERE name LIKE 'base%.xm%' and locks = 'host.lock_mode=aggressive,host.processor_all=1' and state = 1")
        host_info_lock_base2 = self.host_info(query_field='name,state,locks',
                                           query="WHERE name LIKE 'base%.xm%' and locks = 'host.processor_all=1' and state = 1")
        c_base_lock = 0
        c_base_lock2 = 0
        for i in host_info_lock_base:
            c_base_lock += 1
        for i in host_info_lock_base2:
            c_base_lock2 += 1
        return c_base_lock+c_base_lock2

    def host_base_down(self):
        host_info_down_base = self.host_info(query_field='name,state,locks',
                                          query="WHERE name LIKE 'base%.xm%' and state = 257 ")
        c_base_down = 0
        for i in host_info_down_base:
            c_base_down += 1
        return c_base_down

    def host_base_idle(self):
        host_info_idle_base = self.host_info(query_field='name,state,locks',
                                          query="WHERE name LIKE 'base%.xm%' and locks = 'host.processor_all=0' and state = 1 and used_cpus=0")
        host_info_idle_base2 = self.host_info(query_field='name,state,locks',
                                          query="WHERE name LIKE 'base%.xm%' and locks = '' and state = 1 and used_cpus=0")
        c_base_idle = 0
        c_base_idle2 = 0
        for i in host_info_idle_base:
            c_base_idle += 1
        for i in host_info_idle_base2:
            c_base_idle2 += 1
        return c_base_idle+c_base_idle2

    def host_base_run(self):
        host_info_run_base = self.host_info(query_field='name,state,locks',
                                         query="WHERE name LIKE 'base%.xm%' and locks = 'host.processor_all=0' and state = 1 and used_cpus=1")
        host_info_run_base2 = self.host_info(query_field='name,state,locks',
                                         query="WHERE name LIKE 'base%.xm%' and locks = 'host.lock_mode=aggressive,host.processor_all=0' and state = 1 and used_cpus=1")
        c_base_run = 0
        c_base_run2 = 0
        for i in host_info_run_base:
            c_base_run += 1
        for i in host_info_run_base2:
            c_base_run2 += 1
        return c_base_run+c_base_run2

    def host_node_lock(self):
        host_info_lock_node = self.host_info(query_field='name,state,locks',
                                          query="WHERE name LIKE 'node%.xm%' and locks = 'host.lock_mode=aggressive,host.processor_all=1' and state = 1")
        host_info_lock_node2 = self.host_info(query_field='name,state,locks',
                                           query="WHERE name LIKE 'node%.xm%' and locks = 'host.processor_all=1' and state = 1")
        c_node_lock2 = 0
        c_node_lock = 0
        for i in host_info_lock_node:
            c_node_lock += 1
        for i in host_info_lock_node2:
            c_node_lock2 += 1
        return c_node_lock+c_node_lock2

    def host_node_down(self):
        host_info_down_node = self.host_info(query_field='name,state,locks',
                                          query="WHERE name LIKE 'node%.xm%' and state = 257 ")
        c_node_down = 0
        for i in host_info_down_node:
            c_node_down += 1
        return c_node_down

    def host_node_idle(self):
        host_info_idle_node = self.host_info(query_field='name,state,locks',
                                          query="WHERE name LIKE 'node%.xm%' AND locks='host.processor_all=0' AND state=1 AND used_cpus=0")
        host_info_idle_node2 = self.host_info(query_field='name,state,locks',
                                           query="WHERE name LIKE 'node%.xm%' AND locks='' AND state=1 AND used_cpus=0")
        c_node_idle = 0
        c_node_idle2 = 0
        for i in host_info_idle_node:
            c_node_idle += 1
            # print 'idle',i
        for i in host_info_idle_node2:
            c_node_idle2 += 1
        return c_node_idle+c_node_idle2

    def host_node_run(self):
        host_info_run_node = self.host_info(query_field='name,state,locks',
                                         query="WHERE name LIKE 'node%.xm%' and locks = 'host.processor_all=0' and state = 1 and used_cpus=1")
        host_info_run_node2 = self.host_info(query_field='name,state,locks',
                                          query="WHERE name LIKE 'node%.xm%' and locks = 'host.lock_mode=aggressive,host.processor_all=0' and state = 1 and used_cpus=1")
        c_node_run = 0
        c_node_run2 = 0
        for i in host_info_run_node:
            c_node_run2 += 1

        for i in host_info_run_node2:
            c_node_run += 1

        return c_node_run+c_node_run2

    def host_base_48_all(self):
        def split2dict(string, split_char=',', split_dict='='):
            if not string:
                assert 'please input string'
                return
            if not split_char:
                assert 'please input split char'
                return
            return {i.split(split_dict)[0]: i.split(split_dict)[1] for i in string.split(split_char)}

        host_48 = []
        host_os = []
        host_info_base = self.host_info(query_field='name,state,locks,properties', query="WHERE name LIKE 'base%'")
        for i in range(len(host_info_base)):
            dict = split2dict(host_info_base[i][3], ',', '=')
            if 'host.ram' in dict:
                host_os.append(dict['host.os'])
                if int(dict['host.ram']) >= 48:
                    host_48.append('%s  %s' % (host_info_base[i][0][:8], dict['host.ram']))
            if 'host.ram' not in dict:
                host_os.append(dict['host.os'])
        return len(host_48)

    def host_base_48_down(self):
        def split2dict(string, split_char=',', split_dict='='):
            if not string:
                assert 'please input string'
                return
            if not split_char:
                assert 'please input split char'
                return
            return {i.split(split_dict)[0]: i.split(split_dict)[1] for i in string.split(split_char)}


        host_48 = []
        host_os = []
        host_info_base = self.host_info(query_field='name,state,locks,properties', query="WHERE name LIKE 'base%'")
        for i in range(len(host_info_base)):
            dict = split2dict(host_info_base[i][3], ',', '=')
            if 'host.ram' in dict:
                host_os.append(dict['host.os'])
                if int(dict['host.ram']) >= 48 and (host_info_base[i][1] == 257):
                    host_48.append('%s  %s' % (host_info_base[i][0][:8], dict['host.ram']))
            if 'host.ram' not in dict:
                host_os.append(dict['host.os'])
        return len(host_48)


if __name__ == '__main__':
    t = time.time()
    conn = qbMySQL.dbConnection()
    cursor = conn.cursor()
    q = QubeDatabase()
    '''
    for i in q.job_query(query="WHERE id>277607"):
        print i
        if i[0] == 270001:
           print i
    q.close()
    '''
    print time.time() - t
    #cursor.execute("use 830qube")
    #cursor.execute("show tables")
    cursor.execute('show databases')
    cursor.execute('SELECT * FROM jobidmax')



    #cursor.execute('select groups from job')
    #cursor.execute('select timeout from hold')


    #cursor.execute("show variables like '%qube%'")
    #cursor.execute('use qube')
    #cursor.execute('show tables')
    #cursor.execute('describe job')

    for i in cursor.fetchall():
        print i
    conn.close()
    # cursor.execute("DESC log")
    # for i, k in enumerate(cursor.fetchall()):
    #     print i, k
